/*
 Navicat Premium Data Transfer

 Source Server         : 阿里云数据库
 Source Server Type    : MySQL
 Source Server Version : 80036
 Source Host           : rm-bp11v67xrqjhldn8ceo.mysql.rds.aliyuncs.com:3306
 Source Schema         : test_device

 Target Server Type    : MySQL
 Target Server Version : 80036
 File Encoding         : 65001

 Date: 30/09/2025 09:52:03
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for device
-- ----------------------------
DROP TABLE IF EXISTS `device`;
CREATE TABLE `device` (
                          `id` int NOT NULL AUTO_INCREMENT COMMENT '自增主键',
                          `code` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备唯一码',
                          `name` varchar(128) COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备名称',
                          `product_id` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品id，关联产品表',
                          `access_method` varchar(32) COLLATE utf8mb4_general_ci DEFAULT 'mqtt' COMMENT '接入方式:mqtt等',
                          `auth_method` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '认证方式',
                          `auth_password` varchar(46) COLLATE utf8mb4_general_ci NOT NULL COMMENT '认证密码',
                          `status` tinyint NOT NULL DEFAULT '0' COMMENT '设备的状态：0-未认证、1-认证、2-启用、3-未启用',
                          `comment` varchar(512) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备描述',
                          `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
                          `updated_by` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
                          `created_time` datetime DEFAULT NULL COMMENT '创建时间',
                          `created_by` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
                          `deleted` tinyint DEFAULT '0' COMMENT '删除标识：0-未删除，1-已删除',
                          PRIMARY KEY (`id`),
                          UNIQUE KEY `uk_device_code` (`code`) COMMENT '设备唯一码唯一',
                          KEY `idx_product_id` (`product_id`) COMMENT '商品ID索引，用于关联查询'
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='设备表';

-- ----------------------------
-- Records of device
-- ----------------------------
BEGIN;
INSERT INTO `device` (`id`, `code`, `name`, `product_id`, `access_method`, `auth_method`, `auth_password`, `status`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (1, 'device000001', '智能温湿度传感器', '1', 'MQTT', 'DEVICEPASSWORD', 'Passw0rd', 2, NULL, '2025-09-29 15:18:18', 'Wb', '2025-09-28 16:57:28', 'Wb', 0);
INSERT INTO `device` (`id`, `code`, `name`, `product_id`, `access_method`, `auth_method`, `auth_password`, `status`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (2, 'device000002', '车削钻头', '1', 'MQTT', 'DEVICEPASSWORD', 'Passw0rd', 2, 'sdsss', '2025-09-29 18:25:33', 'Wb', '2025-09-29 09:07:02', 'Wb', 0);
INSERT INTO `device` (`id`, `code`, `name`, `product_id`, `access_method`, `auth_method`, `auth_password`, `status`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (3, 'device000003', '大型起重机', '2', 'MQTT', 'DEVICEPASSWORD', 'Passw0rd', 0, NULL, '2025-09-29 18:23:41', 'Wb', '2025-09-29 14:40:18', 'Wb', 0);
COMMIT;

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
                           `id` int NOT NULL AUTO_INCREMENT COMMENT '自增主键',
                           `code` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品条码',
                           `name` varchar(128) COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品名称',
                           `type_id` int NOT NULL COMMENT '商品类型：机床等',
                           `access_method` varchar(32) COLLATE utf8mb4_general_ci DEFAULT 'mqtt' COMMENT '接入方式:mqtt等',
                           `auth_method` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '认证方式',
                           `auth_password` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '认证密码',
                           `status` tinyint DEFAULT '0' COMMENT '产品状态：0-禁用，1-启用（控制产品是否可被设备关联）',
                           `comment` varchar(512) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品描述',
                           `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
                           `updated_by` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
                           `created_time` datetime DEFAULT NULL COMMENT '创建时间',
                           `created_by` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
                           `deleted` tinyint DEFAULT '0' COMMENT '删除标识：0-未删除，1-已删除',
                           PRIMARY KEY (`id`),
                           UNIQUE KEY `uk_code` (`code`) COMMENT '商品条码唯一'
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='产品表';

-- ----------------------------
-- Records of product
-- ----------------------------
BEGIN;
INSERT INTO `product` (`id`, `code`, `name`, `type_id`, `access_method`, `auth_method`, `auth_password`, `status`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (1, 'super000001', '车削设备', 1, 'MQTT', 'DEVICEPASSWORD', 'Passw0rd', 0, '密密麻麻', '2025-09-29 18:22:47', 'Wb', '2025-09-27 16:34:30', 'Wb', 0);
INSERT INTO `product` (`id`, `code`, `name`, `type_id`, `access_method`, `auth_method`, `auth_password`, `status`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (2, 'super000002', '起重机', 4, 'MQTT', 'DEVICEPASSWORD', 'Passw0rd', 0, NULL, '2025-09-29 15:02:33', 'Wb', '2025-09-28 09:48:04', 'Wb', 0);
INSERT INTO `product` (`id`, `code`, `name`, `type_id`, `access_method`, `auth_method`, `auth_password`, `status`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (3, 'super000003', '油漆', 2, 'MQTT', 'DEVICEPASSWORD', 'Passw0rd', 1, NULL, '2025-09-29 15:02:11', 'Wb', '2025-09-28 15:23:16', 'Wb', 0);
COMMIT;

-- ----------------------------
-- Table structure for product_property
-- ----------------------------
DROP TABLE IF EXISTS `product_property`;
CREATE TABLE `product_property` (
                                    `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
                                    `product_id` int NOT NULL COMMENT '产品id',
                                    `device_id` int DEFAULT NULL COMMENT '设备id',
                                    `property` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '属性标识',
                                    `property_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '属性名称（如：温度、湿度、风速等）',
                                    `data_type` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据类型（如：int, double等）',
                                    `data_range` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据范围',
                                    `precisions` int DEFAULT NULL COMMENT '精度值设置',
                                    `access_mode` varchar(20) COLLATE utf8mb4_general_ci NOT NULL COMMENT '读取方式（read:只读, write:只写, report:上报）',
                                    `value` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '属性值',
                                    `comment` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '描述信息',
                                    `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
                                    `updated_by` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
                                    `created_time` datetime NOT NULL COMMENT '创建时间',
                                    `created_by` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建人',
                                    `deleted` int DEFAULT '0' COMMENT '删除标识（0:未删除, 1:已删除）',
                                    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='产品属性表';

-- ----------------------------
-- Records of product_property
-- ----------------------------
BEGIN;
INSERT INTO `product_property` (`id`, `product_id`, `device_id`, `property`, `property_name`, `data_type`, `data_range`, `precisions`, `access_mode`, `value`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (1, 1, 2, '温', '温度', 'integer', '-40~85', 1, 'read', '25.6', NULL, '2025-09-29 18:22:47', 'Wb', '2025-09-27 16:34:30', 'Wb', 0);
INSERT INTO `product_property` (`id`, `product_id`, `device_id`, `property`, `property_name`, `data_type`, `data_range`, `precisions`, `access_mode`, `value`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (2, 1, 2, '湿度', '湿度', 'integer', '0~100', 0, 'report', '60', NULL, '2025-09-29 18:22:47', 'Wb', '2025-09-27 16:34:30', 'Wb', 0);
INSERT INTO `product_property` (`id`, `product_id`, `device_id`, `property`, `property_name`, `data_type`, `data_range`, `precisions`, `access_mode`, `value`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (3, 1, 2, '风速', '风速', 'integer', '0~50', 2, 'write', '3.25', NULL, '2025-09-29 18:22:47', 'Wb', '2025-09-27 16:34:30', 'Wb', 0);
INSERT INTO `product_property` (`id`, `product_id`, `device_id`, `property`, `property_name`, `data_type`, `data_range`, `precisions`, `access_mode`, `value`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (4, 2, 3, 'Maximum lifting capacity', '最大起重量', 'integer', '200-300', 1, 'read', '270', NULL, '2025-09-29 14:40:18', 'Wb', '2025-09-28 09:48:04', 'Wb', 0);
INSERT INTO `product_property` (`id`, `product_id`, `device_id`, `property`, `property_name`, `data_type`, `data_range`, `precisions`, `access_mode`, `value`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (5, 2, 3, 'Vehicle weight', '车重', 'integer', '100', 1, 'read', '100', NULL, '2025-09-29 14:40:18', 'Wb', '2025-09-28 09:48:04', 'Wb', 0);
INSERT INTO `product_property` (`id`, `product_id`, `device_id`, `property`, `property_name`, `data_type`, `data_range`, `precisions`, `access_mode`, `value`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (6, 1, 2, '速度', '速度', 'integer', '10000', 1, 'read', '10000', NULL, '2025-09-29 18:22:47', 'Wb', '2025-09-28 13:42:20', 'Wb', 0);
INSERT INTO `product_property` (`id`, `product_id`, `device_id`, `property`, `property_name`, `data_type`, `data_range`, `precisions`, `access_mode`, `value`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (7, 1, 2, '重量', '重量', 'integer', '1000', 0, 'read', '10000', NULL, '2025-09-29 18:22:47', 'Wb', '2025-09-28 13:50:54', 'Wb', 0);
INSERT INTO `product_property` (`id`, `product_id`, `device_id`, `property`, `property_name`, `data_type`, `data_range`, `precisions`, `access_mode`, `value`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (8, 3, 0, 'color', '颜色', 'integer', '1000', 1, 'read', '1000', NULL, NULL, NULL, '2025-09-28 15:23:16', 'Wb', 0);
COMMIT;

-- ----------------------------
-- Table structure for product_type
-- ----------------------------
DROP TABLE IF EXISTS `product_type`;
CREATE TABLE `product_type` (
                                `id` int NOT NULL AUTO_INCREMENT COMMENT '自增主键,类型：0-机床设备 1-涂装设备 2-热处理设备 3-起重设备',
                                `type` tinyint DEFAULT NULL COMMENT '类型：0-机床设备 1-涂装设备 2-热处理设备 3-起重设备',
                                `en_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '英文名称',
                                `cn_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '中文名称',
                                `comment` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '描述',
                                `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
                                `updated_by` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
                                `created_time` datetime NOT NULL COMMENT '创建时间',
                                `created_by` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建人',
                                `deleted` tinyint NOT NULL DEFAULT '0' COMMENT '删除标志：0-存在 1-删除',
                                PRIMARY KEY (`id`),
                                KEY `idx_type` (`type`),
                                KEY `idx_deleted` (`deleted`),
                                KEY `idx_created_time` (`created_time`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='产品类型表';

-- ----------------------------
-- Records of product_type
-- ----------------------------
BEGIN;
INSERT INTO `product_type` (`id`, `type`, `en_name`, `cn_name`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (1, 0, 'machine tool', '机床类设备', '机床类设备类型', NULL, NULL, '2025-09-25 10:30:17', 'system', 0);
INSERT INTO `product_type` (`id`, `type`, `en_name`, `cn_name`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (2, 0, 'coating equipment', '涂装类设备', '涂装类设备类型', NULL, NULL, '2025-09-25 10:30:17', 'system', 0);
INSERT INTO `product_type` (`id`, `type`, `en_name`, `cn_name`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (3, 0, 'heat treatment equipment', '热处理类设备', '热处理类设备类型', NULL, NULL, '2025-09-25 10:30:17', 'system', 0);
INSERT INTO `product_type` (`id`, `type`, `en_name`, `cn_name`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (4, 0, 'lifting category', '起重类设备', '起重类设备类型', NULL, NULL, '2025-09-25 10:30:17', 'system', 0);
INSERT INTO `product_type` (`id`, `type`, `en_name`, `cn_name`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (5, 0, 'welding equipment', '焊接类设备', '焊接类设备', NULL, NULL, '2025-09-25 11:11:09', 'Wb', 0);
INSERT INTO `product_type` (`id`, `type`, `en_name`, `cn_name`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (6, 0, 'car', '汽车', '测试啊啊啊', '2025-09-27 14:27:53', 'Wb', '2025-09-25 15:15:17', 'Wb', 1);
INSERT INTO `product_type` (`id`, `type`, `en_name`, `cn_name`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (7, 0, 'test1', '测试2', '测试2', '2025-09-27 14:27:55', 'Wb', '2025-09-25 15:15:24', 'Wb', 1);
INSERT INTO `product_type` (`id`, `type`, `en_name`, `cn_name`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (8, 0, 'Testccc', '啥方法', '是多少是的是的', '2025-09-27 14:27:58', 'Wb', '2025-09-26 10:21:40', 'Wb', 1);
INSERT INTO `product_type` (`id`, `type`, `en_name`, `cn_name`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (9, 0, 'test', 'test', 'tests', '2025-09-29 11:22:11', 'Wb', '2025-09-26 14:13:25', 'Wb', 1);
INSERT INTO `product_type` (`id`, `type`, `en_name`, `cn_name`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (10, 0, 'sd', 'sdsdsd', 'sd', '2025-09-27 14:28:02', 'Wb', '2025-09-27 14:02:00', 'Wb', 1);
INSERT INTO `product_type` (`id`, `type`, `en_name`, `cn_name`, `comment`, `updated_time`, `updated_by`, `created_time`, `created_by`, `deleted`) VALUES (11, 0, '哈哈你', '嘻嘻', '买买买', '2025-09-27 14:28:00', 'Wb', '2025-09-27 14:27:06', 'Wb', 1);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
